﻿using Masuit.Tools.Logging;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Data;
using System.IO;

namespace LongGi.WatchFilesChangeWinserver
{
    public class ExcelReader
    {



        public static List<string> ErrorPaths = new List<string>();
        /// <summary>
        /// 读取Excel数据
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="maxHeaderScanRows"></param>
        /// <returns></returns>
        public    DataTable ReadExcelWithDynamicHeader(string filePath, int maxHeaderScanRows = 5)
        {
            try
            {
                using (FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite))
                {
                    IWorkbook workbook = GetWorkbook(filePath, fileStream);
                    ISheet sheet = workbook.GetSheetAt(0); // 获取第一个工作表

                    // 获取实际数据范围
                    int startRow = sheet.FirstRowNum;
                    int endRow = sheet.LastRowNum;
                    int totalCols = GetMaxColumnCount(sheet);
                    // 查找有效的标题行
                    int headerRowIndex = FindValidHeaderRow(sheet, startRow, endRow, totalCols, maxHeaderScanRows);
                    // 创建 DataTable
                    DataTable dataTable = CreateDataTable(sheet, headerRowIndex, totalCols);
                    // 填充数据（从标题行下一行开始）
                    FillDataTable(sheet, dataTable, headerRowIndex + 1, endRow, totalCols);
                    fileStream.Dispose();
                    LogManager.Info($"Excel文档：{filePath} 读取成功 函数：{nameof(ReadExcelWithDynamicHeader)}");

                    if (ErrorPaths.Contains(filePath))
                    {
                        ErrorPaths.Remove(filePath);
                    }
                    return dataTable;
                }

            }
            catch (Exception ex)
            {
                ErrorPaths.Add(filePath);
                LogManager.Info($"函数：{ReadExcelWithDynamicHeader}报错了！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！");
                LogManager.Error(ex);
                return null;
            }
           
           

        }

        // 根据文件扩展名创建对应工作簿
        private IWorkbook GetWorkbook(string filePath, FileStream stream)
        {
            string extension = Path.GetExtension(filePath).ToLower();


            switch (extension)
            {
                case ".xlsx":
                    return new XSSFWorkbook(stream);
                case ".xls":
                    return new HSSFWorkbook(stream);
                default:
                    throw new NotSupportedException("不支持的Excel格式");
            }
        }



        // 获取工作表的最大列数
        private int GetMaxColumnCount(ISheet sheet)
        {
            int maxCols = 0;
            for (int rowIndex = sheet.FirstRowNum; rowIndex <= sheet.LastRowNum; rowIndex++)
            {
                IRow row = sheet.GetRow(rowIndex);
                if (row != null && row.LastCellNum > maxCols)
                {
                    maxCols = row.LastCellNum;
                }
            }
            return maxCols;
        }

        // 查找有效的标题行（所有列都有非空值）
        private int FindValidHeaderRow(ISheet sheet, int startRow, int endRow, int totalCols, int maxScanRows)
        {
            int scanToRow = Math.Min(startRow + maxScanRows, endRow);

            for (int rowIndex = startRow; rowIndex <= scanToRow; rowIndex++)
            {
                IRow row = sheet.GetRow(rowIndex);
                if (row == null) continue;

                bool isValid = true;
                for (int colIndex = 0; colIndex < totalCols; colIndex++)
                {
                    ICell cell = row.GetCell(colIndex);
                    if (cell == null || IsCellValueEmpty(cell))
                    {
                        isValid = false;
                        break;
                    }
                }

                if (isValid) return rowIndex;
            }

            return -1; // 未找到有效标题行
        }

        // 检查单元格是否为空
        private bool IsCellValueEmpty(ICell cell)
        {
            if (cell == null) return true;

            switch (cell.CellType)
            {
                case CellType.Blank:
                    return true;
                case CellType.String:
                    return string.IsNullOrWhiteSpace(cell.StringCellValue);
                default:
                    return false;
            }
        }

        // 创建 DataTable 并设置列名
        private DataTable CreateDataTable(ISheet sheet, int headerRowIndex, int totalCols)
        {
            DataTable dataTable = new DataTable();
            List<string> columnNames = new List<string>();

            if (headerRowIndex >= 0)
            {
                // 使用找到的标题行设置列名
                IRow headerRow = sheet.GetRow(headerRowIndex);
                for (int colIndex = 0; colIndex < totalCols; colIndex++)
                {
                    ICell cell = headerRow.GetCell(colIndex);
                    string colName = (cell != null) ? GetCellValue(cell).Trim() : "";

                    // 处理空列名
                    if (string.IsNullOrEmpty(colName))
                    {
                        colName = $"Column{colIndex}";
                    }

                    // 处理重复列名
                    string uniqueName = colName;
                    int counter = 1;
                    while (columnNames.Contains(uniqueName))
                    {
                        uniqueName = $"{colName}_{counter}";
                        counter++;
                    }

                    dataTable.Columns.Add(uniqueName);
                    columnNames.Add(uniqueName);
                }
            }
            else
            {
                // 未找到有效标题行，使用默认列名
                for (int colIndex = 0; colIndex < totalCols; colIndex++)
                {
                    dataTable.Columns.Add($"Column{colIndex}");
                }
            }

            return dataTable;
        }

        // 填充 DataTable 数据
        private void FillDataTable(ISheet sheet, DataTable dataTable, int startRowIndex, int endRowIndex, int totalCols)
        {
            for (int rowIndex = startRowIndex; rowIndex <= endRowIndex; rowIndex++)
            {
                IRow row = sheet.GetRow(rowIndex);
                if (row == null) continue; // 跳过空行

                DataRow dataRow = dataTable.NewRow();
                bool hasData = false;

                for (int colIndex = 0; colIndex < totalCols; colIndex++)
                {
                    ICell cell = row.GetCell(colIndex);
                    if (cell != null && !IsCellValueEmpty(cell))
                    {
                        dataRow[colIndex] = GetCellValue(cell);
                        hasData = true;
                    }
                }

                if (hasData) dataTable.Rows.Add(dataRow);
            }
        }

        // 获取单元格值（通用方法）
        private string GetCellValue(ICell cell)
        {
            if (cell == null) return string.Empty;

            switch (cell.CellType)
            {
                case CellType.String:
                    return cell.StringCellValue;
                case CellType.Numeric:
                    return DateUtil.IsCellDateFormatted(cell)
                        ? cell.DateCellValue?.ToString("yyyy-MM-dd HH:mm:ss")
                        : cell.NumericCellValue.ToString();
                case CellType.Boolean:
                    return cell.BooleanCellValue.ToString();
                case CellType.Formula:
                    try
                    {
                        return GetCellValue(cell, cell.CachedFormulaResultType);
                    }
                    catch
                    {
                        return cell.ToString();
                    }
                default:
                    return cell.ToString();
            }
        }

        // 重载方法处理公式计算结果
        private string GetCellValue(ICell cell, CellType resultType)
        {
            switch (resultType)
            {
                case CellType.String:
                    return cell.StringCellValue;
                case CellType.Numeric:
                    return cell.NumericCellValue.ToString();
                case CellType.Boolean:
                    return cell.BooleanCellValue.ToString();
                default:
                    return cell.ToString();
            }
        }

    }
}
